package data.dao.daoImplementation;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import data.dao.*;
import data.dto.*;
import data.searchdto.*;

public class LeaveTypeDAOImpl implements LeaveTypeDAO {

	// @Override
	// public ArrayList<LeaveTypeDTO> findLeaveType(LeaveTypeDTO leavesearchdto)
	// throws LeaveDAOException, java.sql.SQLException, SQLException {

	// String sql = "SELECT * FROM leavetype ORDER BY id ASC ";
	// conn = util.Database.getInstance().getConnection();
	// ArrayList<LeaveTypeDTO> searchResults =
	// searchMatching(leavesearchdto,conn);

	// return searchResults;
	// }

	@Override
	public ArrayList<LeaveTypeDTO> findAllLeaveType() throws LeaveDAOException,
			java.sql.SQLException, SQLException {
		// TODO Auto-generated method stub
		ArrayList<LeaveTypeDTO> searchResults;
		try {
			String sql = "SELECT * FROM leavetype ORDER BY id ASC ";

			searchResults = constructList(util.Database.getInstance()
					.getConnection().prepareStatement(sql));

			util.Database.getInstance().closeConnection();

		} finally {

		}
		return searchResults;
	}

	@Override
	public void insertLeaveDetailType(LeaveTypeDTO LeaveDetail)
			throws LeaveDAOException, java.sql.SQLException, SQLException {

		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO leavetype ( id, name, createdby, "
					+ "createdon, modifiedby, modifiedon, "
					+ "status) VALUES (?, ?, ?, ?, ?, ?, ?) ";

			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			stmt.setInt(1, LeaveDetail.getId());
			stmt.setString(2, LeaveDetail.getName());
			stmt.setInt(3, util.CurrentUser.getInstance().getUserId());
			stmt.setInt(4, 1);
			stmt.setInt(5, util.CurrentUser.getInstance().getUserId());
			stmt.setInt(6, 1);
			stmt.setInt(7, LeaveDetail.getStatus());

			int rowcount = stmt.executeUpdate();
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void updateLeaveDetailType(LeaveTypeDTO LeaveDetail)
			throws LeaveDAOException, java.sql.SQLException, NotFoundException,
			SQLException {

		String sql = "UPDATE leavetype SET name = ?, "
				+ "modifiedby = ?, modifiedon = ?, status = ? WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setString(1, LeaveDetail.getName());
			stmt.setInt(2, util.CurrentUser.getInstance().getUserId());
			stmt.setTimestamp(3, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(4, LeaveDetail.getStatus());
			stmt.setInt(5, LeaveDetail.getId());

			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void deleteLeaveDetailType(LeaveTypeDTO LeaveDetail)
			throws LeaveDAOException, java.sql.SQLException, NotFoundException,
			SQLException {

		String sql = "DELETE FROM leavetype WHERE id = ?  ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, LeaveDetail.getId());

			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	protected ArrayList<LeaveTypeDTO> constructList(PreparedStatement stmt)
			throws SQLException, java.sql.SQLException {

		ArrayList<LeaveTypeDTO> searchResults = new ArrayList<LeaveTypeDTO>();
		ResultSet result = null;

		try {
			result = stmt.executeQuery();

			while (result.next()) {
				LeaveTypeDTO temp = new LeaveTypeDTO();

				temp.setId(result.getInt("id"));
				temp.setName(result.getString("name"));
				temp.setCreatedBy(result.getInt("createdby"));
				temp.setCreatedOn(result.getTimestamp("createdon"));
				temp.setModifiedBy(result.getInt("modifiedby"));
				temp.setModifiedOn(result.getTimestamp("modifiedon"));
				temp.setStatus((short) 1);

				searchResults.add(temp);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		}

		return searchResults;
	}

	public ArrayList<LeaveTypeDTO> searchMatching(LeaveTypeDTO valueObject)
			throws SQLException, java.sql.SQLException {

		ArrayList<LeaveTypeDTO> searchResults;

		boolean first = true;
		StringBuffer sql = new StringBuffer(
				"SELECT * FROM leavetype WHERE 1=1 ");

		if (valueObject.getId() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND id = ").append(valueObject.getId()).append(" ");
		}

		if (valueObject.getName() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND name LIKE '").append(valueObject.getName())
					.append("%' ");
		}

		if (valueObject.getCreatedBy() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND createdby = ").append(valueObject.getCreatedBy())
					.append(" ");
		}

		if (valueObject.getCreatedOn() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND createdon = ").append(valueObject.getCreatedOn())
					.append(" ");
		}

		if (valueObject.getModifiedBy() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND modifiedby = ").append(valueObject.getModifiedBy())
					.append(" ");
		}

		if (valueObject.getModifiedOn() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND modifiedon = ").append(valueObject.getModifiedOn())
					.append(" ");
		}

		if (valueObject.getStatus() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND status = ").append(valueObject.getStatus())
					.append(" ");
		}

		sql.append("ORDER BY id ASC ");

		// Prevent accidential full table results.
		// Use loadAll if all rows must be returned.
		if (first)
			searchResults = new ArrayList<LeaveTypeDTO>();
		else
			searchResults = constructList(util.Database.getInstance()
					.getConnection().prepareStatement(sql.toString()));

		return searchResults;
	}

}
